package cn.laoshini.dk.dao;

import java.lang.reflect.Field;
import java.lang.reflect.Modifier;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Collections;
import java.util.List;
import java.util.Map;

import com.google.common.collect.Lists;
import org.apache.commons.lang3.StringUtils;

import cn.laoshini.dk.constant.QueryConditionKeyEnum;
import cn.laoshini.dk.dao.update.SqlWrapper;
import cn.laoshini.dk.dao.update.UpdateSqlWrapper;
import cn.laoshini.dk.domain.common.Tuple;
import cn.laoshini.dk.domain.query.AbstractQueryCondition;
import cn.laoshini.dk.domain.query.PageQueryCondition;
import cn.laoshini.dk.exception.DaoException;
import cn.laoshini.dk.util.CollectionUtil;
import cn.laoshini.dk.util.StringUtil;

import static cn.laoshini.dk.dao.SqlBuilder.COMMA;

/**
 * SQL构建工具类，使用PreparedStatement类型的SQL
 *
 * @author fagarine
 */
public class PreparedStatementSqlBuilder {
    public static final String QM = "?";

    private PreparedStatementSqlBuilder() {
    }

    private static String getColumnName(Class<?> entityType, String fieldName) {
        return SqlBuilder.toColumnName(entityType, fieldName);
    }

    private static String getTableNameByTypeOnCheck(Class<?> entityType) {
        if (entityType == null) {
            throw new DaoException("entity.type.null", "实体类类型不能为空");
        }

        if (!entityType.isAnnotationPresent(TableMapping.class)) {
            throw new DaoException("not.entity.type", "不是数据库表对应的实体类类型:" + entityType.getName());
        }

        TableMapping annotation = entityType.getAnnotation(TableMapping.class);
        return StringUtil.isNotEmptyString(annotation.value()) ? annotation.value() : entityType.getSimpleName();
    }

    private static int appendFieldsInsertSql(Class<?> entityType, StringBuilder fieldStr, StringBuilder valueStr) {
        Field[] fields = entityType.getDeclaredFields();
        int count = 0;
        for (Field field : fields) {
            if (Modifier.isStatic(field.getModifiers())) {
                continue;
            }
            TableKey tableKey = field.getAnnotation(TableKey.class);
            if (tableKey != null && tableKey.autoIncrement()) {
                continue;
            }

            if (count++ > 0) {
                fieldStr.append(COMMA);
                valueStr.append(COMMA).append(" ");
            }
            fieldStr.append("`").append(getColumnName(entityType, field.getName())).append("`");
            valueStr.append(QM);
        }
        return count;
    }

    /**
     * 创建PreparedStatement类型的插入SQL
     *
     * @param entity 实体类对象
     * @param <E> 实体类类型
     * @return 如果不是实体类类型，将会抛出异常
     */
    public static <E> Tuple<String, Tuple<int[], List<Object>>> newInsertSql(E entity) {
        if (entity == null) {
            return null;
        }

        String tableName = getTableNameByTypeOnCheck(entity.getClass());
        StringBuilder insertSql = new StringBuilder("INSERT INTO `").append(tableName).append("` (");
        StringBuilder fieldStr = new StringBuilder();
        StringBuilder valueStr = new StringBuilder();

        Class<?> entityType = entity.getClass();
        int count = appendFieldsInsertSql(entityType, fieldStr, valueStr);

        Field[] fields = entityType.getDeclaredFields();
        int[] types = new int[count];
        List<Object> params = new ArrayList<>(count);
        int i = 0;
        for (Field field : fields) {
            if (Modifier.isStatic(field.getModifiers())) {
                continue;
            }
            TableKey tableKey = field.getAnnotation(TableKey.class);
            if (tableKey != null && tableKey.autoIncrement()) {
                continue;
            }
            types[i++] = SqlBuilder.toJdbcType(field.getType());
            params.add(getFieldValue(entity, field));
        }

        String sql = insertSql.append(fieldStr).append(") values (").append(valueStr).append(")").toString();
        return new Tuple<>(sql, new Tuple<>(types, params));
    }

    /**
     * 创建批量插入数据的PreparedStatement类型的SQL
     *
     * @param entityType 实体类类型
     * @param size 插入数量
     * @return 如果数量 &lt= 0，将会返回null                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   0                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                               ，                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                               将会返回null
     */
    public static String newBatchInsertSql(Class<?> entityType, int size) {
        String tableName = getTableNameByTypeOnCheck(entityType);
        if (size <= 0) {
            return null;
        }

        StringBuilder insertSql = new StringBuilder("INSERT INTO `").append(tableName).append("` (");
        StringBuilder fieldStr = new StringBuilder();
        StringBuilder valueStr = new StringBuilder("(");

        appendFieldsInsertSql(entityType, fieldStr, valueStr);
        valueStr.append(")");

        String values = StringUtils.repeat(valueStr.toString(), COMMA, size);
        return insertSql.append(fieldStr).append(") values ").append(values).toString();
    }

    /**
     * 返回所有对象中各字段的类型和值
     *
     * @param entities 实体对象集合
     * @param <E> 实体类类型
     * @return 如果集合为空，将会返回null
     */
    public static <E> Tuple<int[], List<Object>> getAllPreparedStatementParams(List<E> entities) {
        if (entities == null || entities.isEmpty()) {
            return null;
        }

        Field[] fields = entities.get(0).getClass().getDeclaredFields();
        int count = 0;
        for (Field field : fields) {
            TableKey tableKey = field.getAnnotation(TableKey.class);
            if (tableKey != null && tableKey.autoIncrement()) {
                continue;
            }
            if (!Modifier.isStatic(field.getModifiers())) {
                count++;
            }
        }
        count = count * entities.size();
        int[] types = new int[count];
        List<Object> params = new ArrayList<>(count);
        int index = 0;
        for (E entity : entities) {
            for (Field field : fields) {
                TableKey tableKey = field.getAnnotation(TableKey.class);
                if (tableKey != null && tableKey.autoIncrement()) {
                    continue;
                }
                if (Modifier.isStatic(field.getModifiers())) {
                    continue;
                }
                types[index++] = SqlBuilder.toJdbcType(field.getType());
                params.add(getFieldValue(entity, field));
            }
        }
        return new Tuple<>(types, params);
    }

    /**
     * 创建批量插入PreparedStatement类型的SQL，并返回对应参数的类型和值
     *
     * @param entities 实体对象集合
     * @param <E> 实体类类型
     * @return 如果集合为空，将会返回null
     */
    public static <E> Tuple<String, Tuple<int[], List<Object>>> newBatchInsertSql(List<E> entities) {
        if (entities == null || entities.isEmpty()) {
            return null;
        }

        return new Tuple<>(newBatchInsertSql(entities.get(0).getClass(), entities.size()),
                getAllPreparedStatementParams(entities));
    }

    /**
     * 创建PreparedStatement类型的更新SQL，并返回对应参数的类型和值
     *
     * @param entity 实体对象
     * @param keys 更新时的键
     * @param <E> 实体类类型
     * @return 如果找不到作为键的字段，将会抛出异常
     */
    public static <E> Tuple<String, Tuple<int[], List<Object>>> newUpdateSql(E entity, List<String> keys) {
        if (entity == null) {
            return null;
        }
        String tableName = getTableNameByTypeOnCheck(entity.getClass());
        if (CollectionUtil.isEmpty(keys)) {
            List<String> primaryKeys = SqlBuilder.getTableKeyFields(entity);
            if (CollectionUtil.isEmpty(primaryKeys)) {
                throw new DaoException("no.condition.key", "没有找到可以作为更新条件的字段名，不能更新:" + entity);
            }
            keys = primaryKeys;
        }

        Class<?> entityType = entity.getClass();
        Field[] fields = entityType.getDeclaredFields();
        int count = keys.size();
        for (Field field : fields) {
            if (!Modifier.isStatic(field.getModifiers()) && !keys.contains(field.getName())) {
                count++;
            }
        }
        StringBuilder updateSql = new StringBuilder("UPDATE `").append(tableName).append("` SET ");
        StringBuilder valueStr = new StringBuilder(count << 4);
        StringBuilder conditionStr = new StringBuilder(keys.size() << 4);

        int[] types = new int[count];
        Object[] params = new Object[count];
        int keyIndex = 0;
        int valueIndex = 0;
        int valueCount = count - keys.size();
        for (Field field : fields) {
            if (Modifier.isStatic(field.getModifiers())) {
                continue;
            }

            if (valueStr.length() > 0) {
                valueStr.append(COMMA);
            }

            String fieldName = field.getName();
            String columnName = getColumnName(entityType, fieldName);
            if (keys.contains(fieldName)) {
                int index = keyIndex + valueCount;
                params[index] = getFieldValue(entity, field);
                types[index] = SqlBuilder.toJdbcType(field.getType());
                if (conditionStr.length() > 0) {
                    conditionStr.append(" AND ");
                }
                conditionStr.append("`").append(columnName).append("`=").append(QM);
                keyIndex++;
            } else {
                types[valueIndex] = SqlBuilder.toJdbcType(field.getType());
                params[valueIndex++] = getFieldValue(entity, field);
                valueStr.append("`").append(columnName).append("`=").append(QM);
            }
        }

        String sql = updateSql.append(valueStr).append(" WHERE ").append(conditionStr).toString();
        return new Tuple<>(sql, new Tuple<>(types, Lists.newArrayList(params)));
    }

    /**
     * 创建PreparedStatement类型的更新SQL，并返回对应参数的类型和值
     *
     * @param entity 实体对象
     * @param <E> 实体类类型
     * @return 如果在实体类中找不到作为键的字段，将会抛出异常
     */
    public static <E> Tuple<String, Tuple<int[], List<Object>>> newUpdateSql(E entity) {
        if (entity == null) {
            return null;
        }

        List<String> keys = SqlBuilder.getTableKeyFields(entity);
        if (CollectionUtil.isEmpty(keys)) {
            throw new DaoException("entity.no.key", "实体类中没有标注主键，不能直接更新:" + entity);
        }

        return newUpdateSql(entity, keys);
    }

    /**
     * 创建PreparedStatement类型的更新SQL，并返回对应参数的类型和值
     *
     * @param entity 实体对象
     * @param keys 更新时的键
     * @param <E> 实体类类型
     * @return 如果找不到作为键的字段，将会抛出异常
     */
    public static <E> Tuple<String, Tuple<int[], List<Object>>> newUpdateSql(E entity, String... keys) {
        return newUpdateSql(entity, keys == null ? Collections.emptyList() : Arrays.asList(keys));
    }

    private static void appendCondition(Class<?> entityType, Map<String, Object> condition, int[] types, int typeIndex,
            List<Object> params, StringBuilder conditionStr) {
        for (Map.Entry<String, Object> entry : condition.entrySet()) {
            types[typeIndex++] = SqlBuilder.getJdbcType(entry.getValue());
            params.add(entry.getValue());

            if (conditionStr.length() > 0) {
                conditionStr.append(" AND ");
            }
            String columnName = getColumnName(entityType, entry.getKey());
            conditionStr.append("`").append(columnName).append("`=").append(QM);
        }
    }

    /**
     * 创建PreparedStatement类型的批量更新SQL，并返回对应参数的类型和值
     *
     * @param tableName 表名
     * @param entityType 实体类类型
     * @param condition 更新条件
     * @param updatedColumns 更新内容
     * @return 如果更新内容为空，将会抛出异常
     */
    public static Tuple<String, Tuple<int[], List<Object>>> newBatchUpdateSql(String tableName, Class<?> entityType,
            Map<String, Object> condition, Map<String, Object> updatedColumns) {
        if (CollectionUtil.isEmpty(updatedColumns)) {
            return null;
        }

        int condCount = condition == null ? 0 : condition.size();
        int paramCount = updatedColumns.size() + condCount;
        StringBuilder updateSql = new StringBuilder("UPDATE `").append(tableName).append("` SET ");
        StringBuilder valueStr = new StringBuilder(paramCount << 4);
        StringBuilder conditionStr = new StringBuilder(condCount << 4);
        int[] types = new int[paramCount];
        List<Object> params = new ArrayList<>(paramCount);
        int index = 0;
        for (Map.Entry<String, Object> entry : updatedColumns.entrySet()) {
            types[index++] = SqlBuilder.getJdbcType(entry.getValue());
            params.add(entry.getValue());

            if (index > 0) {
                valueStr.append(COMMA);
            }
            String columnName = getColumnName(entityType, entry.getKey());
            valueStr.append("`").append(columnName).append("`=").append(QM);
        }
        if (CollectionUtil.isNotEmpty(condition)) {
            appendCondition(entityType, condition, types, index, params, conditionStr);
        }

        String sql = updateSql.append(valueStr).append(" WHERE ").append(conditionStr).toString();
        return new Tuple<>(sql, new Tuple<>(types, params));
    }

    /**
     * 创建PreparedStatement类型的批量更新SQL，并返回对应参数的类型和值
     *
     * @param tableName 表名
     * @param condition 更新条件
     * @param updatedColumns 更新内容
     * @return 如果更新内容为空，将会抛出异常
     */
    public static Tuple<String, Tuple<int[], List<Object>>> newBatchUpdateSql(String tableName,
            Map<String, Object> condition, Map<String, Object> updatedColumns) {
        return newBatchUpdateSql(tableName, null, condition, updatedColumns);
    }

    /**
     * 创建PreparedStatement类型的批量更新SQL，并返回对应参数的类型和值
     *
     * @param wrapper 更新SQL条件封装对象
     * @return 如果更新内容为空，将会抛出异常
     */
    public static Tuple<String, Tuple<int[], List<Object>>> newBatchUpdateSql(UpdateSqlWrapper wrapper) {
        return newBatchUpdateSql(wrapper.getTableName(), wrapper.getEntityType(), wrapper.getCondition(),
                wrapper.getUpdateValues());
    }

    /**
     * 创建PreparedStatement类型的删除SQL，并返回对应参数的类型和值
     *
     * @param tableName 表名
     * @param entityType 实体类类型
     * @param condition 删除条件
     * @return 该方法不会返回null
     */
    public static Tuple<String, Tuple<int[], List<Object>>> newDeleteSql(String tableName, Class<?> entityType,
            Map<String, Object> condition) {
        if (StringUtil.isEmptyString(tableName)) {
            tableName = getTableNameByTypeOnCheck(entityType);
        }

        int condCount = condition == null ? 0 : condition.size();
        StringBuilder deleteSql = new StringBuilder("DELETE FROM `").append(tableName).append("` ");
        StringBuilder conditionStr = new StringBuilder(condCount << 4);
        int[] types = new int[condCount];
        List<Object> params = new ArrayList<>(condCount);
        int index = 0;
        if (CollectionUtil.isNotEmpty(condition)) {
            appendCondition(entityType, condition, types, index, params, conditionStr);
            deleteSql.append(" WHERE ").append(conditionStr);
        }

        return new Tuple<>(deleteSql.toString(), new Tuple<>(types, params));
    }

    /**
     * 创建PreparedStatement类型的删除SQL，并返回对应参数的类型和值
     *
     * @param tableName 表名
     * @param condition 删除条件
     * @return 该方法不会返回null
     */
    public static Tuple<String, Tuple<int[], List<Object>>> newDeleteSql(String tableName,
            Map<String, Object> condition) {
        return newDeleteSql(tableName, null, condition);
    }

    /**
     * 创建PreparedStatement类型的删除SQL，并返回对应参数的类型和值
     *
     * @param wrapper 删除SQL条件封装对象
     * @return 该方法不会返回null
     */
    public static Tuple<String, Tuple<int[], List<Object>>> newDeleteSql(SqlWrapper wrapper) {
        return newDeleteSql(wrapper.getTableName(), wrapper.getEntityType(), wrapper.getCondition());
    }

    /**
     * 创建PreparedStatement类型的删除SQL，并返回对应参数的类型和值
     *
     * @param entity 实体对象
     * @param <E> 实体类类型
     * @return 如果对象为空，将会返回null，如果不是表映射的实体类，将会抛出异常
     */
    public static <E> Tuple<String, Tuple<int[], List<Object>>> newDeleteSql(E entity) {
        if (entity == null) {
            return null;
        }

        Class<?> entityType = entity.getClass();
        String tableName = getTableNameByTypeOnCheck(entityType);
        List<String> primaryKeys = SqlBuilder.getTableKeyFields(entityType);
        if (CollectionUtil.isEmpty(primaryKeys)) {
            throw new DaoException("no.condition.key", "没有找到可以作为删除条件的主键字段名，不能删除:" + entity);
        }

        StringBuilder deleteSql = new StringBuilder("DELETE FROM `").append(tableName).append("` ");
        StringBuilder conditionStr = new StringBuilder(primaryKeys.size() << 4);
        int[] types = new int[primaryKeys.size()];
        List<Object> params = new ArrayList<>(primaryKeys.size());
        int index = 0;
        for (String key : primaryKeys) {
            try {
                Field field = entityType.getDeclaredField(key);
                Object value = getFieldValue(entity, field);
                types[index++] = SqlBuilder.getJdbcType(value);
                params.add(value);

                if (conditionStr.length() > 0) {
                    conditionStr.append(" AND ");
                }
                String columnName = getColumnName(entityType, key);
                conditionStr.append("`").append(columnName).append("`=").append(QM);
            } catch (NoSuchFieldException e) {
                e.printStackTrace();
            }
        }
        deleteSql.append(" WHERE ").append(conditionStr);

        return new Tuple<>(deleteSql.toString(), new Tuple<>(types, params));
    }

    private static void appendSelectFilter(Class<?> entityType, StringBuilder selectSql, Map<String, Object> filters,
            int[] types, List<Object> params) {
        if (CollectionUtil.isNotEmpty(filters)) {
            StringBuilder conditionStr = new StringBuilder();
            appendCondition(entityType, filters, types, 0, params, conditionStr);
            selectSql.append("WHERE ").append(conditionStr);
        }
    }

    private static String getSelectTableName(Class<?> entityType, Map<String, Object> filters) {
        String tableName;
        if (entityType == null) {
            if (CollectionUtil.isEmpty(filters) || !filters.containsKey(QueryConditionKeyEnum.TABLE_NAME.getKey())) {
                throw new DaoException("table.name.missing", "SQL查询不能没有表名");
            }
            tableName = (String) filters.remove(QueryConditionKeyEnum.TABLE_NAME.getKey());
        } else {
            tableName = getTableNameByTypeOnCheck(entityType);
        }
        return tableName;
    }

    /**
     * 创建PreparedStatement类型的查询SQL，并返回对应参数的类型和值
     *
     * @param entityType 实体类类型
     * @param condition 查询条件
     * @return 如果找不到表名，将会抛出异常
     */
    public static Tuple<String, Tuple<int[], List<Object>>> newSelectSql(Class<?> entityType,
            AbstractQueryCondition condition) {
        Map<String, Object> filters = condition.getFilters();
        String tableName = getSelectTableName(entityType, filters);
        StringBuilder selectSql = new StringBuilder("SELECT * FROM `").append(tableName).append("` ");
        int count = filters == null ? 0 : filters.size();
        int[] types = new int[count];
        List<Object> params = new ArrayList<>(count);
        appendSelectFilter(entityType, selectSql, filters, types, params);
        return new Tuple<>(selectSql.toString(), new Tuple<>(types, params));
    }

    /**
     * 创建PreparedStatement类型的查询SQL，并返回对应参数的类型和值
     *
     * @param condition 查询条件
     * @return 如果找不到表名，将会抛出异常
     */
    public static Tuple<String, Tuple<int[], List<Object>>> newSelectSql(AbstractQueryCondition condition) {
        return newSelectSql(null, condition);
    }

    /**
     * 创建PreparedStatement类型的分页查询SQL，并返回对应参数的类型和值
     *
     * @param entityType 实体类类型
     * @param condition 查询条件
     * @return 如果找不到表名，将会抛出异常
     */
    public static Tuple<String, Tuple<int[], List<Object>>> newPageQuerySql(Class<?> entityType,
            PageQueryCondition condition) {
        Map<String, Object> filters = condition.getFilters();
        String tableName = getSelectTableName(entityType, filters);

        int[] types = null;
        List<Object> params = null;
        StringBuilder conditionStr = new StringBuilder();
        if (CollectionUtil.isNotEmpty(filters)) {
            types = new int[filters.size()];
            params = new ArrayList<>(filters.size());
            appendCondition(entityType, filters, types, 0, params, conditionStr);
        }

        StringBuilder orderByStr = new StringBuilder();
        if (StringUtil.isNotEmptyString(condition.getOrderBy())) {
            orderByStr.append(" ORDER BY `").append(condition.getOrderBy()).append("` ");
            if (StringUtil.isNotEmptyString(condition.getOrderSort())) {
                orderByStr.append(getColumnName(entityType, condition.getOrderSort()));
            }
        }

        String sql = SqlBuilder.appendPageSql(tableName, condition, conditionStr, orderByStr);
        return new Tuple<>(sql, new Tuple<>(types, params));
    }

    /**
     * 创建PreparedStatement类型的分页查询SQL，并返回对应参数的类型和值
     *
     * @param condition 查询条件
     * @return 如果找不到表名，将会抛出异常
     */
    public static Tuple<String, Tuple<int[], List<Object>>> newPageQuerySql(PageQueryCondition condition) {
        return newPageQuerySql(null, condition);
    }

    /**
     * 创建PreparedStatement类型的统计SQL，并返回对应参数的类型和值
     *
     * @param entityType 实体类类型
     * @param condition 查询条件
     * @return 如果找不到表名，将会抛出异常
     */
    public static Tuple<String, Tuple<int[], List<Object>>> newCountSql(Class<?> entityType,
            AbstractQueryCondition condition) {
        Map<String, Object> filters = condition.getFilters();
        String tableName = getSelectTableName(entityType, filters);
        StringBuilder countSql = new StringBuilder("SELECT COUNT(1) FROM `").append(tableName).append("` ");
        int count = filters == null ? 0 : filters.size();
        int[] types = new int[count];
        List<Object> params = new ArrayList<>(count);
        appendSelectFilter(entityType, countSql, filters, types, params);
        return new Tuple<>(countSql.toString(), new Tuple<>(types, params));
    }

    private static Object getFieldValue(Object bean, Field field) {
        boolean access = field.isAccessible();
        field.setAccessible(true);
        try {
            return field.get(bean);
        } catch (IllegalAccessException e) {
            e.printStackTrace();
        } finally {
            field.setAccessible(access);
        }
        return null;
    }
}
